﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using ProjetoDLL.Model;

namespace ProjetoDLL.DAO
{
    public class ClienteDAO : BaseDAO<Cliente>
    {
        private UsuarioDAO uController = new UsuarioDAO();

        public override void Inserir(Cliente obj)
        {
            string strConexao = ConfigurationManager.ConnectionStrings["DB_AW2_PROJETO"].ConnectionString;
            SqlConnection conexao = new SqlConnection(strConexao);

            string query = string.Format("INSERT INTO tb_cliente VALUES('{0}','{1}','A',{2})",
                obj.Nome, obj.CPF, obj.UsuarioCliente.IdUsuario);

            exec_comando(query, conexao);
        }

        public override void Atualizar(Cliente obj)
        {
            string strConexao = ConfigurationManager.ConnectionStrings["DB_AW2_PROJETO"].ConnectionString;
            SqlConnection conexao = new SqlConnection(strConexao);

            string query = string.Format("UPDATE tb_cliente SET nome='{0}', cpf='{1}', status='{2}', fk_usuario={3} WHERE pk_cliente={4}",
                                   obj.Nome, obj.CPF, obj.Status, obj.UsuarioCliente.IdUsuario, obj.IdCliente);

            exec_comando(query, conexao);
        }

        public override void Excluir(Cliente obj)
        {
            string strConexao = ConfigurationManager.ConnectionStrings["DB_AW2_PROJETO"].ConnectionString;
            SqlConnection conexao = new SqlConnection(strConexao);

            string query = string.Format("DELETE FROM tb_cliente WHERE pk_cliente = {0}", obj.IdCliente);

            exec_comando(query, conexao);
        }

        public override Cliente RecuperarUm(int chave)
        {
            string strConexao = ConfigurationManager.ConnectionStrings["DB_AW2_PROJETO"].ConnectionString;
            SqlConnection conexao = new SqlConnection(strConexao);

            int fk_usuario = 0;
            string query;
            SqlDataReader dr;

            Cliente cliente = new Cliente();

            query = string.Format("SELECT * FROM tb_cliente WHERE pk_cliente = {0}", chave);

            SqlCommand cmd = new SqlCommand(query, conexao);

            conexao.Open();
            dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                cliente.IdCliente = Convert.ToInt32(dr["pk_cliente"]);
                cliente.Nome = Convert.ToString(dr["nome"]);
                cliente.CPF = Convert.ToString(dr["cpf"]);
                cliente.Status = Convert.ToString(dr["cliente"]);
                fk_usuario = Convert.ToInt32(dr["fk_usuario"]);
            }

            dr.Close();
            conexao.Close();

            cliente.UsuarioCliente = uController.RecuperarUm(fk_usuario);

            return cliente;
        }


        public Cliente RecuperarUm(Usuario u)
        {
            string strConexao = ConfigurationManager.ConnectionStrings["DB_AW2_PROJETO"].ConnectionString;
            SqlConnection conexao = new SqlConnection(strConexao);

            int fk_usuario = 0;
            string query;
            SqlDataReader dr;

            Cliente cliente = new Cliente();

            query = string.Format("SELECT * FROM tb_cliente WHERE fk_usuario = {0}", u.Numero);

            SqlCommand cmd = new SqlCommand(query, conexao);

            conexao.Open();
            dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                cliente.IdCliente = Convert.ToInt32(dr["pk_cliente"]);
                cliente.Nome = Convert.ToString(dr["nome"]);
                cliente.CPF = Convert.ToString(dr["cpf"]);
                cliente.Status = Convert.ToString(dr["cliente"]);
                fk_usuario = Convert.ToInt32(dr["fk_usuario"]);
            }

            dr.Close();
            conexao.Close();

            cliente.UsuarioCliente = uController.RecuperarUm(fk_usuario);

            return cliente;
        }

        public override List<Cliente> RecuperarTodos()
        {
            string strConexao = ConfigurationManager.ConnectionStrings["DB_AW2_PROJETO"].ConnectionString;
            SqlConnection conexao = new SqlConnection(strConexao);

            List<int> listaFkUsuario = new List<int>();
            List<Cliente> listaEntidades = new List<Cliente>();
            SqlDataReader dr;
            SqlCommand cmd = new SqlCommand("SELECT * FROM tb_cliente", conexao);
            conexao.Open();
            dr = cmd.ExecuteReader();

            Cliente cliente;

            while (dr.Read())
            {
                cliente = new Cliente();
                cliente.IdCliente = Convert.ToInt32(dr["pk_cliente"]);
                cliente.Nome = Convert.ToString(dr["nome"]);
                cliente.CPF = Convert.ToString(dr["cpf"]);
                cliente.Status = Convert.ToString(dr["cliente"]);
                listaFkUsuario.Add(Convert.ToInt32(dr["fk_usuario"]));
                listaEntidades.Add(cliente);
            }

            dr.Close();
            conexao.Close();

            for (int i = 0; i < listaEntidades.Count; i++)
            {
                listaEntidades[i].UsuarioCliente = uController.RecuperarUm(listaFkUsuario[i]);
            }

                return listaEntidades;
        }
    }
}
